﻿Imports System.Data.SQLite
Public Class ESQLiteConnectionException
    Inherits Exception
    Public Sub New()
        MyBase.New()
    End Sub
    Public Sub New(ByVal Msg As String)
        MyBase.New(Msg)
    End Sub
End Class

Public Class TSQLiteConnection
    Implements System.IDisposable
    Public Enum TSQLiteDateTimeFormat As Integer
        Ticks = 0
        ISO8601 = 1
        JulianDay = 2
    End Enum
    Public Enum TSQLiteSynchronous As Integer
        Normal = 0
        Full = 1
        Off = 2
    End Enum
    Public Enum TSQLitePageSize As Integer
        Size512 = 0
        Size1024 = 1
        Size2048 = 2
        Size4096 = 3
        Size8192 = 4
    End Enum
    Public Enum TSQLiteJournalMode As Integer
        Delete = 0
        Persist = 1
        Off = 2
    End Enum
    Private Const SQLITE_VERSION As String = "3"
    Private Const DEFAULT_CACHE_SIZE As Integer = 2000
    Private Const DEFAULT_SYNCHRONOUS As TSQLiteSynchronous = TSQLiteSynchronous.Normal
    Private Const DEFAULT_PAGE_SIZE As TSQLitePageSize = TSQLitePageSize.Size1024
    Private Const DEFAULT_ENLIST As Boolean = True
    Private Const DEFAULT_POOLING As Boolean = False
    Private Const DEFAULT_FAIL_IF_MISSING As Boolean = False
    Private Const DEFAULT_MAX_PAGE_COUNT As Integer = 0
    Private Const DEFAULT_LEGACY_FORMAT As Boolean = False
    Private Const DEFAULT_TIMEOUT As Integer = 30
    Private Const DEFAULT_JOURNAL_MODE As TSQLiteJournalMode = TSQLiteJournalMode.Delete
    Private Const DEFAULT_READ_ONLY As Boolean = False
    Private Const DEFAULT_MAX_POOL_SIZE As Integer = 100
    Private Const DEFAULT_USE_UTF16_ENCODING As Boolean = False
    Private Const DEFAULT_BINARY_GUID As Boolean = False
    Private Const DEFAULT_DATETIME_FORMAT As TSQLiteDateTimeFormat = TSQLiteDateTimeFormat.ISO8601

    Private _Enlist As Boolean
    Private _CacheSize As Integer
    Private _Version As String
    Private _Conn As SQLiteConnection
    Private _FileName As String
    Private _UseUTF16Encoding As Boolean
    Private _DateTimeFormat As TSQLiteDateTimeFormat
    Private _BinaryGUID As Boolean
    Private _Synchronous As TSQLiteSynchronous
    Private _PageSize As TSQLitePageSize
    Private _Password As String
    Private _Pooling As Boolean
    Private _FailIfMissing As Boolean
    Private _MaxPageCount As Integer
    Private _LegacyFormat As Boolean
    Private _Timeout As Integer
    Private _JournalMode As TSQLiteJournalMode
    Private _ReadOnly As Boolean
    Private _MaxPoolSize As Integer
    Private _Command As New SQLiteCommand

    Public Sub New()
        _Conn = New SQLiteConnection
        _Command.Connection = _Conn
        _BinaryGUID = DEFAULT_BINARY_GUID
        _UseUTF16Encoding = DEFAULT_USE_UTF16_ENCODING
        _CacheSize = DEFAULT_CACHE_SIZE
        _DateTimeFormat = TSQLiteDateTimeFormat.ISO8601
        _Synchronous = DEFAULT_SYNCHRONOUS
        _PageSize = DEFAULT_PAGE_SIZE
        _Enlist = DEFAULT_ENLIST
        _Pooling = DEFAULT_POOLING
        _FailIfMissing = DEFAULT_FAIL_IF_MISSING
        _MaxPageCount = DEFAULT_MAX_PAGE_COUNT
        _JournalMode = DEFAULT_JOURNAL_MODE
        _ReadOnly = DEFAULT_READ_ONLY
        _MaxPoolSize = DEFAULT_MAX_POOL_SIZE
        _Version = SQLITE_VERSION
        _FileName = String.Empty
        _Password = String.Empty
        _LegacyFormat = DEFAULT_LEGACY_FORMAT
        _Timeout = DEFAULT_TIMEOUT
    End Sub

    Public Sub BeginTransaction()
        _Command.CommandText = "BEGIN"
        _Command.ExecuteNonQuery()
    End Sub

    Public Sub Commit()
        _Command.CommandText = "COMMIT"
        _Command.ExecuteNonQuery()
    End Sub
    Public Sub Rollback()
        _Command.CommandText = "ROLLBACK"
        _Command.ExecuteNonQuery()
    End Sub

    Public Sub ChangePassword()
        Dim isConnected As Boolean = Me.Connected
        If Not isConnected Then
            Me.Connected = True
        End If
        _Conn.ChangePassword(_Password)
        If Not isConnected Then
            Me.Connected = False
        End If
    End Sub

    Public Property FileName() As String
        Get
            Return _FileName
        End Get
        Set(ByVal value As String)
            _FileName = value
        End Set
    End Property

    Public ReadOnly Property Version()
        Get
            Return _Version
        End Get
    End Property

    Public Property BinaryGUID() As Boolean
        Get
            Return _BinaryGUID
        End Get
        Set(ByVal value As Boolean)
            _BinaryGUID = value
        End Set
    End Property

    Public Property UseUTF16Encoding() As Boolean
        Get
            Return _UseUTF16Encoding
        End Get
        Set(ByVal value As Boolean)
            _UseUTF16Encoding = value
        End Set
    End Property

    Public Property CacheSize() As Integer
        Get
            Return _CacheSize
        End Get
        Set(ByVal value As Integer)
            _CacheSize = value
        End Set
    End Property

    Public Property DateTimeFormat() As TSQLiteDateTimeFormat
        Get
            Return _DateTimeFormat
        End Get
        Set(ByVal value As TSQLiteDateTimeFormat)
            _DateTimeFormat = value
        End Set
    End Property

    Public Property Synchronous() As TSQLiteSynchronous
        Get
            Return _Synchronous
        End Get
        Set(ByVal value As TSQLiteSynchronous)
            _Synchronous = value
        End Set
    End Property

    Public Property PageSize() As TSQLitePageSize
        Get
            Return _PageSize
        End Get
        Set(ByVal value As TSQLitePageSize)
            _PageSize = value
        End Set
    End Property

    Public Property Enlist() As Boolean
        Get
            Return _Enlist
        End Get
        Set(ByVal value As Boolean)
            _Enlist = value
        End Set
    End Property

    Public Property Pooling() As Boolean
        Get
            Return _Pooling
        End Get
        Set(ByVal value As Boolean)
            _Pooling = value
        End Set
    End Property

    Public Property FailIfMissing() As Boolean
        Get
            Return _FailIfMissing
        End Get
        Set(ByVal value As Boolean)
            _FailIfMissing = value
        End Set
    End Property

    Public Property MaxPageCount() As Integer
        Get
            Return _MaxPageCount
        End Get
        Set(ByVal value As Integer)
            _MaxPageCount = value
        End Set
    End Property

    Public Property JournalMode() As TSQLiteJournalMode
        Get
            Return _JournalMode
        End Get
        Set(ByVal value As TSQLiteJournalMode)
            _JournalMode = value
        End Set
    End Property

    Public Property Editable() As Boolean
        Get
            Return Not _ReadOnly
        End Get
        Set(ByVal value As Boolean)
            _ReadOnly = Not value
        End Set
    End Property

    Public Property MaxPoolSize() As Integer
        Get
            Return _MaxPoolSize
        End Get
        Set(ByVal value As Integer)
            _MaxPoolSize = value
        End Set
    End Property

    Public Property LegacyFormat() As Boolean
        Get
            Return _LegacyFormat
        End Get
        Set(ByVal value As Boolean)
            _LegacyFormat = value
        End Set
    End Property

    Public Property Password() As String
        Get
            Return _Password
        End Get
        Set(ByVal value As String)
            _Password = value
        End Set
    End Property

    Public Property Timeout() As Integer
        Get
            Return _Timeout
        End Get
        Set(ByVal value As Integer)
            _Timeout = value
        End Set
    End Property

    Public ReadOnly Property ConnectionString() As String
        Get
            Return GetConnectionString()
        End Get
    End Property

    Public Property Connected() As Boolean
        Get
            Return _Conn.State <> ConnectionState.Closed
        End Get
        Set(ByVal value As Boolean)
            If value Then
                _Conn.ConnectionString = GetConnectionString()
                Try
                    _Conn.Open()
                Catch ex As Exception
                    Throw New Exception(ex.Message)
                End Try
            Else
                _Conn.Close()
            End If
        End Set
    End Property

    Public ReadOnly Property Connection() As SQLiteConnection
        Get
            Return _Conn
        End Get
    End Property

    Private Function GetConnectionString() As String
        Dim sb As New System.Text.StringBuilder
        sb.Append("Data Source=" & _FileName)
        sb.Append(";Version=" & _Version)
        If _UseUTF16Encoding <> DEFAULT_USE_UTF16_ENCODING Then
            If _UseUTF16Encoding Then
                sb.Append(";UseUTF16Encoding=True")
            Else
                sb.Append(";UseUTF16Encoding=False")
            End If
        End If
        If _DateTimeFormat <> DEFAULT_DATETIME_FORMAT Then
            Select Case _DateTimeFormat
                Case TSQLiteDateTimeFormat.ISO8601
                    sb.Append(";UseUTF16Encoding=ISO8601")
                Case TSQLiteDateTimeFormat.JulianDay
                    sb.Append(";UseUTF16Encoding=JulianDay")
                Case TSQLiteDateTimeFormat.Ticks
                    sb.Append(";UseUTF16Encoding=Ticks")
            End Select
        End If
        If _BinaryGUID <> DEFAULT_BINARY_GUID Then
            If _BinaryGUID Then
                sb.Append(";BinaryGUID=On")
            Else
                sb.Append(";BinaryGUID=Off")
            End If
        End If
        If _CacheSize <> DEFAULT_CACHE_SIZE Then
            sb.Append(";Cache Size=" & _CacheSize.ToString)
        End If
        If _Synchronous <> DEFAULT_SYNCHRONOUS Then
            Select Case _Synchronous
                Case TSQLiteSynchronous.Full
                    sb.Append(";Synchronous=Full")
                Case TSQLiteSynchronous.Normal
                    sb.Append(";Synchronous=Normal")
                Case TSQLiteSynchronous.Off
                    sb.Append(";Synchronous=Off")
            End Select
        End If
        If _PageSize <> DEFAULT_PAGE_SIZE Then
            Select Case _PageSize
                Case TSQLitePageSize.Size512
                    sb.Append(";Page Size=512")
                Case TSQLitePageSize.Size1024
                    sb.Append(";Page Size=1024")
                Case TSQLitePageSize.Size2048
                    sb.Append(";Page Size=2048")
                Case TSQLitePageSize.Size4096
                    sb.Append(";Page Size=4096")
                Case TSQLitePageSize.Size8192
                    sb.Append(";Page Size=8192")
            End Select
        End If
        If _Password.Length > 0 Then
            sb.Append(";Password=" & _Password)
        End If
        If _Enlist <> DEFAULT_ENLIST Then
            If _Enlist Then
                sb.Append(";Enlist=Y")
            Else
                sb.Append(";Enlist=N")
            End If
        End If
        If _Pooling <> DEFAULT_POOLING Then
            If _Pooling Then
                sb.Append(";Pooling=True")
            Else
                sb.Append(";Pooling=False")
            End If
        End If
        If _FailIfMissing <> DEFAULT_FAIL_IF_MISSING Then
            If _FailIfMissing Then
                sb.Append(";FailIfMissing=True")
            Else
                sb.Append(";FailIfMissing=False")
            End If
        End If
        If _MaxPageCount <> DEFAULT_MAX_PAGE_COUNT Then
            sb.Append(";Max Page Count=" & _MaxPageCount.ToString)
        End If
        If _LegacyFormat <> DEFAULT_LEGACY_FORMAT Then
            If _LegacyFormat Then
                sb.Append(";Legacy Format=True")
            Else
                sb.Append(";Legacy Format=False")
            End If
        End If
        If _Timeout <> DEFAULT_TIMEOUT Then
            sb.Append(";Default Timeout=" & _Timeout.ToString)
        End If
        If _JournalMode <> DEFAULT_JOURNAL_MODE Then
            Select Case _JournalMode
                Case TSQLiteJournalMode.Delete
                    sb.Append(";Journal Mode=Delete")
                Case TSQLiteJournalMode.Off
                    sb.Append(";Journal Mode=Off")
                Case TSQLiteJournalMode.Persist
                    sb.Append(";Journal Mode=Persist")
            End Select
        End If
        If _ReadOnly <> DEFAULT_READ_ONLY Then
            If _ReadOnly Then
                sb.Append(";Read Only=True")
            Else
                sb.Append(";Read Only=False")
            End If
        End If
        If _MaxPoolSize <> DEFAULT_MAX_POOL_SIZE Then
            sb.Append(";Max Pool Size=" & _MaxPoolSize.ToString)
        End If
        Return sb.ToString
    End Function

    Private disposedValue As Boolean = False        ' 重複する呼び出しを検出するには

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: 他の状態を解放します (マネージ オブジェクト)。
            End If
            _Conn.Dispose()
            ' TODO: ユーザー独自の状態を解放します (アンマネージ オブジェクト)。
            ' TODO: 大きなフィールドを null に設定します。
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' このコードは、破棄可能なパターンを正しく実装できるように Visual Basic によって追加されました。
    Public Sub Dispose() Implements IDisposable.Dispose
        ' このコードを変更しないでください。クリーンアップ コードを上の Dispose(ByVal disposing As Boolean) に記述します。
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class
